﻿Public Class CaratulaPresentacionDAO
    Public Shared Function insertarCaratulaPresentacion(ByVal CaratulaPresentacionFecha As DateTime, ByVal CaratulaPresentacionRecetas As Integer, ByVal CaratulaPresentacionTotalFacturado As Double, ByVal CaratulaPresentacionPorcentajeBonificacion As Double, ByVal CaratulaPresentacionTotalCobrar As Double, ByVal PresentacionId As Integer, ByVal TipoCaratulaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_caratulaPresentacion"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.INSERTAR).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionFecha", CaratulaPresentacionFecha).DbType = DbType.DateTime
                    .AddWithValue("@CaratulaPresentacionRecetas", CaratulaPresentacionRecetas).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionTotalFacturado", CaratulaPresentacionTotalFacturado).DbType = DbType.Double
                    .AddWithValue("@CaratulaPresentacionPorcentajeBonificacion", CaratulaPresentacionPorcentajeBonificacion).DbType = DbType.Double
                    .AddWithValue("@CaratulaPresentacionTotalCobrar", CaratulaPresentacionTotalCobrar).DbType = DbType.Double
                    .AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
                    .AddWithValue("@TipoCaratulaId", TipoCaratulaId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerCaratulasPresentaciones(ByVal CaratulaPresentacionId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_caratulaPresentacion_obtener", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@CaratulaPresentacionId", CaratulaPresentacionId).DbType = DbType.Int32
            da.Fill(ds, "CaratulasPresentaciones")

            Return ds.Tables("CaratulasPresentaciones")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarCaratulaPresentacion(ByVal CaratulaPresentacionId As Integer, ByVal CaratulaPresentacionFecha As DateTime, ByVal CaratulaPresentacionRecetas As Integer, ByVal CaratulaPresentacionTotalFacturado As Double, ByVal CaratulaPresentacionPorcentajeBonificacion As Double, ByVal CaratulaPresentacionTotalCobrar As Double, ByVal PresentacionId As Integer, ByVal TipoCaratulaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_caratulaPresentacion"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ACTUALIZAR).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionId", CaratulaPresentacionId).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionFecha", CaratulaPresentacionFecha).DbType = DbType.DateTime
                    .AddWithValue("@CaratulaPresentacionRecetas", CaratulaPresentacionRecetas).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionTotalFacturado", CaratulaPresentacionTotalFacturado).DbType = DbType.Double
                    .AddWithValue("@CaratulaPresentacionPorcentajeBonificacion", CaratulaPresentacionPorcentajeBonificacion).DbType = DbType.Double
                    .AddWithValue("@CaratulaPresentacionTotalCobrar", CaratulaPresentacionTotalCobrar).DbType = DbType.Double
                    .AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
                    .AddWithValue("@TipoCaratulaId", TipoCaratulaId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()

            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarCaratulaPresentacion(ByVal CaratulaPresentacionId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_caratulaPresentacion"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ELIMINAR).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionId", CaratulaPresentacionId).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionFecha", Constante.FECHAVACIA).DbType = DbType.DateTime
                    .AddWithValue("@CaratulaPresentacionRecetas", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@CaratulaPresentacionTotalFacturado", Constante.MENOSUNO).DbType = DbType.Double
                    .AddWithValue("@CaratulaPresentacionPorcentajeBonificacion", Constante.MENOSUNO).DbType = DbType.Double
                    .AddWithValue("@CaratulaPresentacionTotalCobrar", Constante.MENOSUNO).DbType = DbType.Double
                    .AddWithValue("@PresentacionId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@TipoCaratulaId", Constante.MENOSUNO).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With

            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerCaratulasXOS(ByVal ObraSocialXPlanId As Integer, ByVal AnoId As Integer, ByVal MesId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_caratulaPresentacion_obtenerXOS", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
            da.SelectCommand.Parameters.AddWithValue("@AnoId", AnoId).DbType = DbType.Int32
            da.SelectCommand.Parameters.AddWithValue("@MesId", MesId).DbType = DbType.Int32
            da.Fill(ds, "CaratulasPresentaciones")

            Return ds.Tables("CaratulasPresentaciones")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerIntermediaXPresentacion(ByVal PresentacionId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_caratulaPresentacion_obtenerIntermediaXPresentacion", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
            da.Fill(ds, "CaratulasPresentaciones")

            Return ds.Tables("CaratulasPresentaciones")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerCerradasXOS(ByVal ObraSocialXPlanId As Integer, ByVal AnoId As Integer, ByVal MesId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_caratulaPresentacion_obtenerCerradasXOS", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
            da.SelectCommand.Parameters.AddWithValue("@AnoId", AnoId).DbType = DbType.Int32
            da.SelectCommand.Parameters.AddWithValue("@MesId", MesId).DbType = DbType.Int32
            da.Fill(ds, "CaratulasPresentaciones")

            Return ds.Tables("CaratulasPresentaciones")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerCaratulasXPresentacion(ByVal PresentacionId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_Caratulas_ObtenerXPresentacion", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@PresentacionId", PresentacionId).DbType = DbType.Int32
            da.Fill(ds, "Caratulas")

            Return ds.Tables("Caratulas")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

End Class
